﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Data.SqlTypes;
using System.Data.OracleClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
namespace GeradorMassaDados.Bovespa.Business
{
    public class DataAcess : IDataAcess
    {
        #region Properties

        private string ProviderName { get; set; }

        private string ConnectionString1 { get; set; }

        #endregion

        #region Constructor

        public DataAcess()
        {
            var section = ConfigurationManager.ConnectionStrings;
            var reader = new AppSettingsReader();

            ProviderName = section["DbTester"].ProviderName;
            ConnectionString1 = string.Format(
                section["DbTester"].ConnectionString,
                reader.GetValue("DBUser", typeof(string)),
                reader.GetValue("DBPwd", typeof(string))
            );
        }

        #endregion

        #region Methods

        public DateTime GetOracleDate()
        {
            DateTime returns;

            using (var cnn = InitializeConnection())
            using (var cmd = cnn.CreateCommand())
            {
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select TO_Date(sysDate, 'dd/mm/yyyy hh24:mi:ss') from dual";

                returns = (DateTime)cmd.ExecuteScalar();

                cnn.Close();
            }
            return returns;
        }

        public string[] GetUserNames()
        {
            List<string> users = new List<string>();

            using (var cnn = InitializeConnection())
            using (var cmd = cnn.CreateCommand())
            {
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT Terminal FROM DBA_AUDIT_TRAIL WHERE OWNER = 'SINABASE' GROUP BY Terminal ORDER BY Terminal";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read()) if (!reader.IsDBNull(0)) users.Add(reader.GetString(0));

                    reader.Close();
                }
                cnn.Close();
            }

            return users.ToArray();
        }

        public List<SQLCommandLog> GetCommandLogs(CollectItem item)
        {
            List<SQLCommandLog> sqlCommandItem = new List<SQLCommandLog>();

            using (var cnn = InitializeConnection())
            using (var cmd = cnn.CreateCommand())
            {
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                    string.Concat(
                        "SELECT OS_USERNAME, EXTENDED_TIMESTAMP, SQL_TEXT, SQL_BIND FROM DBA_AUDIT_TRAIL WHERE Terminal = :Terminal AND",                    
                        " OWNER = 'SINABASE' AND OBJ_NAME NOT LIKE 'TMP%' ORDER BY EXTENDED_TIMESTAMP");

                cmd.Parameters.Add(new OracleParameter("Terminal", OracleType.VarChar) { Value = item.Terminal });
                //cmd.Parameters.Add(new OracleParameter("StartTime", OracleType.VarChar) { Value = item.StartTime.ToString("dd/MM/yyyy hh:mm:ss") });
                //cmd.Parameters.Add(new OracleParameter("EndTime", OracleType.VarChar) { Value = item.EndTime.ToString("dd/MM/yyyy hh:mm:ss") });

                using (var reader = cmd.ExecuteReader())
                {
                    int count = 1;
                    while (reader.Read())
                    {
                        sqlCommandItem.Add(new SQLCommandLog()
                            {
                                Extend = !reader.IsDBNull(1) ? reader.GetDateTime(1) : DateTime.MinValue,
                                SQLText = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty,
                                SQLBind = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty,
                                RowNumber = count
                            });
                        count++;
                    }
                    reader.Close();
                }
                cnn.Close();
            }

            //Isso é uma solução porca mas como eh interno, vai ser o que tem para hj
            var enumerable = sqlCommandItem.Where(
                    (commandArg, index) =>
                    {
                        if (commandArg.Extend >= item.StartTime && commandArg.Extend <= item.EndTime)
                            return true;
                        else
                            return false;
                    });
            return new List<SQLCommandLog>(enumerable);
        }

        DbConnection InitializeConnection()
        {
            var db = new OracleDatabase(this.ConnectionString1);
            var cnn = db.CreateConnection();

            using (var cmd = cnn.CreateCommand())
            {
                cnn.ConnectionString = this.ConnectionString1;
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "alter session set nls_date_format =  'dd/mm/yyyy hh24:mi:ss'";

                cnn.Open();
                cmd.ExecuteNonQuery();
            }

            return cnn;
        }

        #endregion
    }
}
